import pymysql

class DBManager:
    def __init__(self,host,user,password,database,charset):
        self.__host = host
        self.__user = user
        self.__password = password
        self.__database = database
        self.__charset = charset

    def create_table(self,table_name,create_sql):
        conn = pymysql.connect(host=self.__host,user=self.__user,password=self.__password,database=self.__database,charset=self.__charset)
        cursor = conn.cursor()
        drop_sql = f"drop table if exists `{table_name}`"
        cursor.execute(drop_sql)
        cursor.execute(create_sql)
        conn.commit()
        cursor.close()
        conn.close()

    def create_table_dict(self,table_name,table_columns):
        conn = pymysql.connect(host=self.__host,user=self.__user,password=self.__password,database=self.__database,charset=self.__charset)
        cursor = conn.cursor()
        drop_sql = f"drop table if exists `{table_name}`"
        # 拼接列定义部分
        columns_definition = ", ".join([f"`{col}` {definition}" for col, definition in table_columns.items()])
        # 拼接完整的 CREATE TABLE 语句
        create_sql = f"CREATE TABLE `{table_name}` ({columns_definition});"
        cursor.execute(drop_sql)
        cursor.execute(create_sql)
        conn.commit()
        cursor.close()
        conn.close()

    def insert_data(self,sql,data):
        conn = pymysql.connect(host=self.__host,user=self.__user,password=self.__password,database=self.__database,charset=self.__charset)
        cursor = conn.cursor()
        cursor.execute(sql,data)
        conn.commit()
        cursor.close()
        conn.close()

    def insert_datas(self,sql,datas):
        conn = pymysql.connect(host=self.__host,user=self.__user,password=self.__password,database=self.__database,charset=self.__charset)
        cursor = conn.cursor()
        cursor.executemany(sql,datas)
        conn.commit()
        cursor.close()
        conn.close()


    def select_data(self,sql):
        conn = pymysql.connect(host=self.__host,user=self.__user,password=self.__password,database=self.__database,charset=self.__charset)
        cursor = conn.cursor()
        cursor.execute(sql)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
        return result

    def update_data(self,sql,data):
        conn = pymysql.connect(host=self.__host,user=self.__user,password=self.__password,database=self.__database,charset=self.__charset)
        cursor = conn.cursor()
        cursor.execute(sql,data)
        conn.commit()
        cursor.close()
        conn.close()

    def delete_data(self,sql,data):
        conn = pymysql.connect(host=self.__host,user=self.__user,password=self.__password,database=self.__database,charset=self.__charset)
        cursor = conn.cursor()
        cursor.execute(sql,data)
        conn.commit()
        cursor.close()
        conn.close()
